package github.sf.fw.tools.db.impl;


import github.sf.fw.tools.db.AbstractTableDataReader;
import github.sf.fw.tools.db.DSProperty;
import github.sf.fw.tools.db.connector.JdbcConnector;
import github.sf.fw.tools.db.impl.mysql.MysqlConstants;
import github.sf.fw.tools.db.model.ResultData;
import github.sf.fw.tools.db.model.ResultRow;
import github.sf.fw.tools.db.model.TableDataWithPage;
import github.sf.fw.tools.db.model.TableInfo;
import github.sf.fw.tools.db.sql.ISqlUtils;
import github.sf.fw.utils.StrUtil;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.function.Consumer;

public abstract class RdbmsTableDataReader extends AbstractTableDataReader {

    protected final int PER_MAX_ROW = 10000;

    public RdbmsTableDataReader(DSProperty dsProperty) {
        super(dsProperty);
    }

    public TableDataWithPage loadTableData(TableInfo tableInfo, int page, int size) throws SQLException {
        String allSql = tableInfo.getTableExtraInfo().getQuerySql();
        if (StrUtil.isEmpty(allSql)) {
            if (StrUtil.isEmpty(tableInfo.getName())) {
                return null;
            }
            allSql = ISqlUtils.selectAll(tableInfo.getDbName(), tableInfo.getName());
            allSql = allSql + " order by " + "a_." + tableInfo.getTableExtraInfo().getOrderByColumn();
        }
        allSql = trimSql(allSql);
        JdbcConnector jdbcConnector = new JdbcConnector(dsProperty);
        String s = ISqlUtils.concatName(tableInfo.getDbName(), tableInfo.getName());
        List<ResultRow> countResult = jdbcConnector.doQuery(StrUtil.format(MysqlConstants.QUERY_TABLE_COUNT, s), null);
        ResultData resultData = countResult.get(0).get(0);
        int tableRowCount = Math.toIntExact((long) resultData.getColumnValue());
//        String limit = PagerUtils.limit(allSql, DbType.mysql, (page - 1) * size, size);
        String limit = getLimitSentence(allSql, (page - 1) * size, size);
        List<ResultRow> resultRows = jdbcConnector.doQuery(limit, null);
        return TableDataWithPage.of(tableRowCount, resultRows);
    }

    public TableDataWithPage loadTableDataWithScript(String queryScript, int page, int size) throws SQLException {
        if (StrUtil.isEmpty(queryScript)) {
            return null;
        }
        queryScript = trimSql(queryScript);
        JdbcConnector jdbcConnector = new JdbcConnector(dsProperty);
        List<ResultRow> countResult = jdbcConnector.doQuery(StrUtil.format(MysqlConstants.QUERY_SQL_COUNT, queryScript), null);
        ResultData resultData = countResult.get(0).get(0);
        int tableRowCount = resultData.getNumberColumnValue();
//        String limit = PagerUtils.limit(queryScript, DbType.mysql, (page - 1) * size, size);
        String limit = getLimitSentence(queryScript, (page - 1) * size, size);
        List<ResultRow> resultRows = jdbcConnector.doQuery(limit, null);
        return TableDataWithPage.of(tableRowCount, resultRows);
    }

    public List<ResultRow> loadTableData(TableInfo tableInfo) throws SQLException {
        String allSql = tableInfo.getTableExtraInfo().getQuerySql();
        if (StrUtil.isEmpty(allSql)) {
            return null;
        }
        JdbcConnector jdbcConnector = new JdbcConnector(dsProperty);
        String s = ISqlUtils.concatName(tableInfo.getDbName(), tableInfo.getName());
        List<ResultRow> resultRows = jdbcConnector.doQuery(StrUtil.format(MysqlConstants.QUERY_TABLE_COUNT, s), null);
        ResultData resultData = resultRows.get(0).get(0);
        Long tableRowCount = (Long) resultData.getColumnValue();
        long readCount = (tableRowCount / PER_MAX_ROW) + 1;
        List<ResultRow> resultRowList = new ArrayList<>();
        int start = 0;
        for (long i = 0; i < readCount; i++) {
            int end = start + PER_MAX_ROW;
            String sql = wrapLimit(tableInfo.getTableExtraInfo().getQuerySql(), start, end);
            resultRowList.addAll(jdbcConnector.doQuery(sql, null));
            start = end;
        }
        return resultRowList;
    }


    public List<ResultRow> loadTableDataWithScript(String querySql) throws SQLException {
        if (StrUtil.isEmpty(querySql)) {
            return null;
        }
        querySql = trimSql(querySql);
        JdbcConnector jdbcConnector = new JdbcConnector(dsProperty);
        List<ResultRow> resultRows = jdbcConnector.doQuery(StrUtil.format(MysqlConstants.QUERY_SQL_COUNT, querySql), null);
        ResultData resultData = resultRows.get(0).get(0);
        Long tableRowCount = (Long) resultData.getColumnValue();
        long readCount = (tableRowCount / PER_MAX_ROW) + 1;
        List<ResultRow> resultRowList = new ArrayList<>();
        int start = 0;
        for (long i = 0; i < readCount; i++) {
            int end = start + PER_MAX_ROW;
            String sql = wrapLimit(querySql, start, end);
            resultRowList.addAll(jdbcConnector.doQuery(sql, null));
            start = end;
        }
        jdbcConnector.close();
        return resultRowList;
    }


    protected abstract String getLimitSentence(String sql, int offset, int count);

    protected abstract String wrapLimit(String sql, int start, int end);

    public String trimSql(String sql) {
        if (StrUtil.nonEmpty(sql)) {
            if (sql.endsWith(";")) {
                return sql.substring(0, sql.length() - 1);
            }
        }
        return sql;
    }

    @Override
    public void transfer(TableInfo tableInfo, Consumer<List<ResultRow>> dataConsumer) throws SQLException {
        String allSql = tableInfo.getTableExtraInfo().getQuerySql();
        if (StrUtil.isEmpty(allSql)) {
            return;
        }
        JdbcConnector jdbcConnector = new JdbcConnector(dsProperty);
        String s = ISqlUtils.concatName(tableInfo.getDbName(), tableInfo.getName());
        List<ResultRow> resultRows = jdbcConnector.doQuery(StrUtil.format(MysqlConstants.QUERY_TABLE_COUNT, s), null);
        ResultData resultData = resultRows.get(0).get(0);
        Long tableRowCount = (Long) resultData.getColumnValue();
        long readCount = (tableRowCount / PER_MAX_ROW) + 1;
//        List<ResultRow> resultRowList = new ArrayList<>();
        int start = 0;
        for (long i = 0; i < readCount; i++) {
            int end = start + PER_MAX_ROW;
            String sql = wrapLimit(tableInfo.getTableExtraInfo().getQuerySql(), start, end);
            List<ResultRow> rows = jdbcConnector.doQuery(sql, null);
            dataConsumer.accept(rows);
            start = end;
        }

    }
}
